﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SQLite;
using System.Data;
using Models;
using Server.Common;

namespace Server.DAL
{
    public static class BillSrv
    {
        #region 转换

        private static BillEntryModel DrToBill(DataRow dr)
        {
            if (dr is null)
                return null;
            BillEntryModel bem = new BillEntryModel
            {
                ChargeCustCode = Comm.Cdr<string>(dr, "ChargeCustCode"),
                ChargeCustId = Comm.Cdr<int>(dr, "ChargeCustId"),
                ChargeCustName = Comm.Cdr<string>(dr, "ChargeCustName"),
                ChargeDate = Comm.Cdr<string>(dr, "ChargeDate"),
                ChargeFlag = Comm.Cdr<byte>(dr, "ChargeFlag"),
                ChargeFlagString = Comm.Cdr<string>(dr, "ChargeFlagString"),
                ChargeMakerId = Comm.Cdr<int>(dr, "ChargeMakerId"),
                ChargeMakerName = Comm.Cdr<string>(dr, "ChargeMakerName"),
                ChargeMakerUser = Comm.Cdr<string>(dr, "ChargeMakerUser"),
                CodeFull = Comm.Cdr<string>(dr, "CodeFull"),
                CodeGenDate = Comm.Cdr<string>(dr, "CodeGenDate"),
                CodeMain = Comm.Cdr<string>(dr, "CodeMain"),
                CodeMakerId = Comm.Cdr<int>(dr, "CodeMakerId"),
                CodeMakerName = Comm.Cdr<string>(dr, "CodeMakerName"),
                CodeMakerUser = Comm.Cdr<string>(dr, "CodeMakerUser"),
                CodeNum = Comm.Cdr<int>(dr, "CodeNum"),
                CodeSuffix = Comm.Cdr<string>(dr, "CodeSuffix"),
                CodeYear = Comm.Cdr<string>(dr, "CodeYear"),
                ErrReason = "",
                CodeId = Comm.Cdr<int>(dr, "CodeId"),
                ProCode = Comm.Cdr<string>(dr, "ProCode"),
                ProDefaultPrice = Comm.Cdr<decimal>(dr, "ProDefaultPrice"),
                ProId = Comm.Cdr<int>(dr, "ProId"),
                ProModel = Comm.Cdr<string>(dr, "ProModel"),
                ProName = Comm.Cdr<string>(dr, "ProName"),
                ProRemark = Comm.Cdr<string>(dr, "ProRemark"),
                SaleCustCode = Comm.Cdr<string>(dr, "SaleCustCode"),
                SaleCustId = Comm.Cdr<int>(dr, "SaleCustId"),
                SaleCustName = Comm.Cdr<string>(dr, "SaleCustName"),
                SaleDate = Comm.Cdr<string>(dr, "SaleDate"),
                SaleFlag = Comm.Cdr<byte>(dr, "SaleFlag"),
                SaleFlagString = Comm.Cdr<string>(dr, "SaleFlagString"),
                SaleMakerId = Comm.Cdr<int>(dr, "SaleMakerId"),
                SaleMakerName = Comm.Cdr<string>(dr, "SaleMakerName"),
                SaleMakerUser = Comm.Cdr<string>(dr, "SaleMakerUser"),
                ChargeAmount = Comm.Cdr<decimal>(dr, "ChargeAmount"),
                CodeStatus = Comm.Cdr<byte>(dr, "CodeStatus"),
                Points = Comm.Cdr<int>(dr, "Points")
            };
            return bem;
        }

        #endregion 转换

        #region 公共

        public static BillEntryModel GetCodeInfo(string fullCode)
        {
            string sqlstr = "select * from vw_bill where CodeFull=@CodeFull";
            SQLiteParameter[] op = new SQLiteParameter[]
            {
                new SQLiteParameter{DbType=DbType.String,ParameterName="@CodeFull",Value = fullCode}
            };
            var dt = Comm.DB.Query(sqlstr, op);
            if (dt.Rows.Count < 1)
                return null;
            return DrToBill(dt.Rows[0]);
        }

        #endregion 公共

        #region 销售

        #region 增加销售记录

        public static void AddSaleBill(CustomModel cm, List<BillEntryModel> bem)
        {
            string sqlstr = "update t_code_status set sale_flag=1,sale_date=datetime('now','localtime'),sale_custom=@sale_custom,sale_maker=@sale_maker where code_id=@code_id";
            SQLiteParameter[][] op = new SQLiteParameter[bem.Count][];
            for (int i = 0; i < bem.Count; i++)
            {
                op[i] = new SQLiteParameter[]
                {
                    new SQLiteParameter{DbType=DbType.Int32,ParameterName="@sale_custom",Value=cm.Id},
                    new SQLiteParameter{DbType=DbType.Int32,ParameterName="@sale_maker",Value=bem[i].SaleMakerId},
                    new SQLiteParameter {DbType=DbType.Int32,ParameterName="@code_id",Value=bem[i].CodeId}
                };
            }
            Comm.DB.ExecTrans(sqlstr, op);
        }

        #endregion 增加销售记录

        #endregion 销售

        #region 兑换

        // 增加兑换记录
        public static void AddChargeBill(CustomModel cm, List<BillEntryModel> bem)
        {
            string sqlstr = "update t_code_status set charge_flag=1,charge_date=datetime('now','localtime'),charge_custom=@charge_custom,charge_maker=@charge_maker,charge_amount=@charge_amount,charge_point=@charge_point where code_id=@code_id";
            SQLiteParameter[][] op = new SQLiteParameter[bem.Count][];
            for (int i = 0; i < bem.Count; i++)
            {
                op[i] = new SQLiteParameter[]
                {
                    new SQLiteParameter{DbType=DbType.Int32,ParameterName="@code_id",Value=bem[i].CodeId},
                    new SQLiteParameter {DbType=DbType.Int32,ParameterName="@charge_custom",Value=cm.Id},
                    new SQLiteParameter {DbType=DbType.Int32,ParameterName="@charge_maker",Value=bem[i].ChargeMakerId},
                    new SQLiteParameter {DbType=DbType.Decimal,ParameterName="@charge_amount",Value=bem[i].ChargeAmount},
                    new SQLiteParameter {DbType=DbType.Int32,ParameterName ="@charge_point",Value=bem[i].Points}
                };
            }
            Comm.DB.ExecTrans(sqlstr, op);
        }

        #endregion 兑换

        #region 积分操作

        //转换
        private static PointBillModel DrToPoint(DataRow dr)
        {
            if (dr is null)
                return null;
            return new PointBillModel
            {
                BillDate = Comm.Cdr<string>(dr, "use_date"),
                CustId = Comm.Cdr<int>(dr, "cust_id"),
                CustName = Comm.Cdr<string>(dr, "c_name"),
                CustPhone = Comm.Cdr<string>(dr, "c_phone"),
                Id = Comm.Cdr<int>(dr, "id"),
                MakerId = Comm.Cdr<int>(dr, "maker"),
                MakerName = Comm.Cdr<string>(dr, "real_name"),
                Remark = Comm.Cdr<string>(dr, "remark"),
                UsePoint = Comm.Cdr<int>(dr, "use_point")
            };
        }

        //读积分单
        public static PointBillModel GetPointBill(int id)
        {
            string sqlstr = $"select t.id,t.cust_id,t1.c_name,t1.c_phone,t.use_date,t.use_point,t.remark,t.maker,t2.real_name  from t_pointbill t left join t_custom t1 on t.cust_id=t1.id left join t_user t2 on t.maker=t2.id where id={id}";
            var dt = Comm.DB.Query(sqlstr);
            if (dt is null)
                return null;
            if (dt.Rows.Count < 1)
                return null;
            return DrToPoint(dt.Rows[0]);
        }

        //读积分单列表
        public static List<PointBillModel> GetPointBillList(string sDate, string eDate, string custName = "", int maker = 0)
        {
            string sqlstr = $"select t.id,t.cust_id,t1.c_name,t1.c_phone,t.use_date,t.use_point,t.remark,t.maker,t2.real_name  from t_pointbill t left join t_custom t1 on t.cust_id=t1.id left join t_user t2 on t.maker=t2.id where t.use_date>='{sDate}' and t.use_date<='{eDate}'";
            if (!string.IsNullOrEmpty(custName))
                sqlstr += $" and t1.c_name like '%{custName}%'";
            if (maker > 0)
                sqlstr += $" and t.maker={maker}";
            var dt = Comm.DB.Query(sqlstr);
            if (dt is null)
                return null;
            if (dt.Rows.Count < 1)
                return null;
            List<PointBillModel> l = new List<PointBillModel>();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                var dr = dt.Rows[i];
                l.Add(DrToPoint(dr));
            }
            return l;
        }

        //保存积分单
        public static void SavePointBill(PointBillModel pm)
        {
            string sqlstr = $"insert into t_pointbill(cust_id,use_point,remark,maker) values({pm.CustId},{pm.UsePoint},'{pm.Remark}',{pm.MakerId})";
            Comm.DB.Exec(sqlstr);
        }

        #endregion 积分操作

        #region 报表

        //产品汇总表
        public static List<ProSummaryRptModel> GetProSummary(string sDate, string eDate, int proId = 0)
        {
            string sqlstr = $"select ProCode,ProName,Sum(SaleFlag) SaleCount,Sum(ChargeFlag) ChargeCount,Sum(ChargeAmount) ChargeAmount1 from vw_bill where (ChargeFlag=1 or SaleFlag=1)";
            string grp = " group by ProId;";
            string cond = "";
            sDate = Convert.ToDateTime(sDate).ToString("yyyy-MM-dd 00:00:00");
            eDate = Convert.ToDateTime(eDate).ToString("yyyy-MM-dd 23:59:59");
            cond += $" and ((SaleDate>='{sDate}' and SaleDate<='{eDate}') or (ChargeDate>='{sDate}' and ChargeDate<='{eDate}'))";
            if (proId > 0)
            {
                //固定产品
                cond += " and ProId=" + proId;
            }
            sqlstr += cond + grp;
            var dt = Comm.DB.Query(sqlstr);
            if (dt.Rows.Count < 1)
                return null;
            List<ProSummaryRptModel> l = new List<ProSummaryRptModel>();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                var dr = dt.Rows[i];
                ProSummaryRptModel psr = new ProSummaryRptModel
                {
                    ChargeAmount = Comm.Cdr<decimal>(dr, "ChargeAmount1"),
                    ChargeCount = Comm.Cdr<int>(dr, "ChargeCount"),
                    ProName = Comm.Cdr<string>(dr, "ProName"),
                    ProCode = Comm.Cdr<string>(dr, "ProCode"),
                    SaleCount = Comm.Cdr<int>(dr, "SaleCount")
                };
                l.Add(psr);
            }
            return l;
        }

        //兑换汇总表
        public static List<ChargeSummaryRptModel> GetChargeSummary(string sDate, string eDate, int proId = 0, int custCate = 0, int custId = 0)
        {
            string sqlstr = "select ChargeCustCode CustCode,SaleCustName CustName,ProCode,ProName,Count(*) ChargeCount,Sum(ChargeAmount) ChargeAmount from vw_bill where ChargeFlag=1";
            string grp = " group by ChargeCustId,ProId;";
            string cond = "";
            sDate = Convert.ToDateTime(sDate).ToString("yyyy-MM-dd 00:00:00");
            eDate = Convert.ToDateTime(eDate).ToString("yyyy-MM-dd 23:59:59");
            cond += $" and ChargeDate>='{sDate}' and ChargeDate<='{eDate}'";
            if (proId > 0)
            {
                //指定产品
                cond += " and ProId=" + proId;
            }
            if (custCate > 0 && custId > 0)
            {
                //指定兑换人
                cond += " and ChargeCustId=" + custId;
            }
            else if (custCate > 0 && custId == 0)
            {
                //指定客户分类
                cond += " and ChargeCustCate=" + custCate;
            }
            sqlstr += cond + grp;
            var dt = Comm.DB.Query(sqlstr);
            List<ChargeSummaryRptModel> l = new List<ChargeSummaryRptModel>();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                var dr = dt.Rows[i];
                ChargeSummaryRptModel csr = new ChargeSummaryRptModel
                {
                    ChargeAmount = Comm.Cdr<decimal>(dr, "ChargeAmount"),
                    ChargeCount = Comm.Cdr<int>(dr, "ChargeCount"),
                    CustCode = Comm.Cdr<string>(dr, "CustCode"),
                    CustName = Comm.Cdr<string>(dr, "CustName"),
                    ProCode = Comm.Cdr<string>(dr, "ProCode"),
                    ProName = Comm.Cdr<string>(dr, "ProName")
                };
                l.Add(csr);
            }
            return l;
        }

        //销售汇总表
        public static List<SaleSummaryRptModel> GetSaleSummary(string sDate, string eDate, int custCate = 0, int custId = 0, int proId = 0)
        {
            string sqlstr = "select SaleCustCode CustCode,SaleCustName CustName,ProCode,ProName,Count(*) SaleCount from vw_bill where SaleFlag=1";
            string grp = " group by SaleCustId,ProId;";
            string cond = "";
            sDate = Convert.ToDateTime(sDate).ToString("yyyy-MM-dd 00:00:00");
            eDate = Convert.ToDateTime(eDate).ToString("yyyy-MM-dd 23:59:59");
            cond += $" and SaleDate>='{sDate}' and SaleDate<='{eDate}'";
            if (custId > 0)
            {
                //指定客户
                cond += $" and SaleCustId={custId}";
            }
            else if (custCate > 0)
            {
                //指定客户类别
                cond += $" and SaleCustCate={custCate}";
            }
            if (proId > 0)
                cond += $" and ProId={proId}";
            sqlstr += cond + grp;
            var dt = Comm.DB.Query(sqlstr);
            if (dt.Rows.Count < 1)
                return null;
            List<SaleSummaryRptModel> l = new List<SaleSummaryRptModel>();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                var dr = dt.Rows[i];
                SaleSummaryRptModel srm = new SaleSummaryRptModel
                {
                    CustCode = Comm.Cdr<string>(dr, "CustCode"),
                    CustName = Comm.Cdr<string>(dr, "CustName"),
                    ProCode = Comm.Cdr<string>(dr, "ProCode"),
                    ProName = Comm.Cdr<string>(dr, "ProName"),
                    SaleCount = Comm.Cdr<int>(dr, "SaleCount")
                };
                l.Add(srm);
            }
            return l;
        }

        //销售明细
        public static List<BillEntryModel> GetSaleDetail(string sDate, string eDate, int proId = 0, int custCate = 0, int custId = 0)
        {
            string sqlstr = "select * from vw_bill where SaleFlag=1";
            sDate = Convert.ToDateTime(sDate).ToString("yyyy-MM-dd 00:00:00");
            eDate = Convert.ToDateTime(eDate).ToString("yyyy-MM-dd 23:59:59");
            sqlstr += $" and SaleDate>='{sDate}' and SaleDate<='{eDate}'";
            if (proId > 0)
                sqlstr += $" and ProId={proId}";
            if (custId > 0)
                sqlstr += $" and SaleCustId={custId}";
            else if (custCate > 0)
                sqlstr += $" and SaleCustCate={custCate}";
            var dt = Comm.DB.Query(sqlstr);
            if (dt.Rows.Count < 1)
                return null;
            List<BillEntryModel> l = new List<BillEntryModel>();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                var dr = dt.Rows[i];
                l.Add(DrToBill(dr));
            }
            return l;
        }

        //兑换明细
        public static List<BillEntryModel> GetChargeDetail(string sDate, string eDate, int proId = 0, int custCate = 0, int custId = 0)
        {
            string sqlstr = "select * from vw_bill where ChargeFlag=1";
            sDate = Convert.ToDateTime(sDate).ToString("yyyy-MM-dd 00:00:00");
            eDate = Convert.ToDateTime(eDate).ToString("yyyy-MM-dd 23:59:59");
            sqlstr += $" and ChargeDate>='{sDate}' and ChargeDate<='{eDate}'";
            if (proId > 0)
                sqlstr += " and ProId=" + proId;
            if (custId > 0)
                sqlstr += " and ChargeCustId=" + custId;
            else if (custCate > 0)
                sqlstr += " and ChargeCustCate=" + custCate;
            var dt = Comm.DB.Query(sqlstr);
            if (dt.Rows.Count < 0)
                return null;
            List<BillEntryModel> l = new List<BillEntryModel>();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                var dr = dt.Rows[i];
                l.Add(DrToBill(dr));
            }
            return l;
        }

        #endregion 报表
    }
}